<?php
namespace App\Logic\Tool;

use App\Models\Basics\ExchangeRate;
use App\Models\MonthlyReport\Payment\AdsDSP;
use App\Models\MonthlyReport\Payment\AdsMonthly;
use App\Models\MonthlyReport\Payment\AdsVBC;
use App\Models\MonthlyReportCommon\Cost\PriceBasics;
use App\Models\Product\ProductRakutenSku;
use App\Models\Purchase\PurchaseOrderDetail;
use App\Models\Basics\SysPlatAccount;
use App\Models\Basics\SysArea;
use App\Models\Basics\SysCountry;
use App\Models\Inventory\AdjustAreaTransit;
use App\Models\Inventory\AreaCn;
use App\Models\MonthlyReport\Cost\LogisticsBasics;
use App\Models\MonthlyReport\Cost\OrderBasics;
use App\Models\MonthlyReport\Tmp\TmpOrder;
use App\Models\OverSeasBa\InventoryBox;
use App\Models\OverSeasBa\InventoryBoxFNSku;
use App\Models\OverSeasIn\Delivery;
use App\Models\OverSeasIn\DeliveryBox;
use App\Models\OverSeasIn\DeliveryBoxDetail;
use App\Models\OverSeasIn\DeliveryDetail;
use App\Models\Product\ProductSku;
use App\Models\Rakuten\RakutenOrderItem;
use App\Models\SalesReport\PaymentClassify as TPaymentClassify;
use App\Models\SalesReport\PaymentArea as TPaymentArea;
use App\Models\SalesReport\PaymentCNSummary as TPaymentCNSummary;
use App\Models\SalesReport\PaymentCountry as TPaymentCountry;
use App\Models\SalesReport\PaymentDetail;
use App\Logic\SalesReport\PaymentDetail as LPaymentDetail;
use App\Models\SalesReport\PaymentDetail as TPaymentDetail;
use App\Models\SalesReport\PaymentSeller as TPaymentSeller;
use App\Models\SalesReport\PaymentTNSummary as TPaymentTNSummary;
use App\Models\SsBasic\AreaClassifyEstimate;
use App\Models\SsBasic\AreaSkuEstimate;
use App\Models\SsBasic\AreaSkuStock;
use App\Models\SsBasic\CountryClassifyEstimate;
use App\Models\SsBasic\CountrySkuEstimate;
use App\Models\Stocking\StockingSku;
use App\Models\Stocking\StockingSkuRatio;
use App\Models\SalesReport\PaymentDetailJP;
use App\Models\Inventory\PublicCn;
use Illuminate\Support\Facades\DB;
use App\Util\RExcel;
class ToolHelper
{
    private static function _upPaymentDetailForSpend(){
        $n = 0;
        $m = 0;
        TPaymentDetail::query()
            ->where('report_date','2024-11-01')
            ->where('is_common',0)
            ->orderBy('id')
            ->chunk(800, function ($data) use(&$n,&$m){
                foreach ($data as $_data){
                    if($_data->is_common == 1 && (!$_data->area_id || !$_data->psku_id)){
                        continue;
                    }else{
                        $spend_old = $_data->spend;
                        $spend = LPaymentDetail::_getSpend($_data->report_date,$_data->country_id,$_data->seller_sku);
                        if($spend_old == $spend){
                            continue;
                        }
                        $spend_rmb_old = $_data->spend_rmb;
                        $_data->spend = $spend;
                        $_data->spend_rmb = round($_data->spend * $_data->rate_odds,3);
                        $profit_rmb_old = $_data->profit_rmb;
                        $diff = $spend_rmb_old - $_data->spend_rmb;
                        $_data->profit_rmb = round($profit_rmb_old + $diff,3);
                        $earning_rmb_old = $_data->earning_rmb;
                        $_data->earning_rmb =  round($earning_rmb_old + $diff,3);
                        $_data->save();
                        $m++;
                    }
                    $n++;
                }
            });
        echo '运行：'.$n.'条'.'，更新：'.$m.'条'.PHP_EOL;
    }

    private static function _checkPaymentDetailADS(){
        AdsMonthly::query()
            ->where('report_date','2024-11-01')
            ->orderBy('id')
            ->chunk(100, function ($ss){
                foreach ($ss as $_ss){
                    $count = PaymentDetail::query()
                        ->where('report_date',$_ss->report_date)
                        ->where('country_id',$_ss->country_id)
                        ->where('seller_sku',$_ss->seller_sku)
                        ->count();
                    if($count == 0){
                        echo 'AdsMonthly:'.$_ss->country_id.'-'.$_ss->seller_sku.'-'.$_ss->spend.PHP_EOL;
                    }
                }
            });
        AdsVBC::query()
            ->where('report_date','2024-11-01')
            ->orderBy('id')
            ->chunk(100, function ($ss){
                foreach ($ss as $_ss){
                    $count = PaymentDetail::query()
                        ->where('report_date',$_ss->report_date)
                        ->where('country_id',$_ss->country_id)
                        ->where('seller_sku',$_ss->seller_sku)
                        ->count();
                    if($count == 0){
                        echo 'AdsVBC:'.$_ss->country_id.'-'.$_ss->seller_sku.'-'.$_ss->spend.PHP_EOL;
                    }
                }
            });
        AdsDSP::query()
            ->where('report_date','2024-11-01')
            ->orderBy('id')
            ->chunk(100, function ($ss){
                foreach ($ss as $_ss){
                    $count = PaymentDetail::query()
                        ->where('report_date',$_ss->report_date)
                        ->where('country_id',$_ss->country_id)
                        ->where('seller_sku',$_ss->seller_sku)
                        ->count();
                    if($count == 0){
                        echo 'AdsDSP:'.$_ss->country_id.'-'.$_ss->seller_sku.'-'.$_ss->spend.PHP_EOL;
                    }
                }
            });
    }

    private static function _upPaymentDetailJP(){
        $rate = [];
        PaymentDetailJP::query()
            ->orderBy('id')
            ->chunk(100, function ($ss) use(&$rate){
                foreach ($ss as $_ss){
                    if(!array_key_exists($_ss->report_date,$rate)){
                        $rate[$_ss->report_date] = ExchangeRate::getRateOddsToRMB('JPY',strtotime($_ss->report_date));
                    }
                    $_ss->rate_odds = $rate[$_ss->report_date];
                    if($_ss->seller_sku){
                        $pd = PaymentDetail::query()
                            ->select('profit_rmb')
                            ->where('report_date',$_ss->report_date)
                            ->where('country_id',$_ss->country_id)
                            ->where('seller_sku',$_ss->seller_sku)
                            ->first();
                        $_ss->profit_rmb = $pd->profit_rmb;
                    }
                    $_ss->save();
                }
            });
    }

    private static function _findDataStockingSku(){
        StockingSku::query()
            ->orderBy('id')
            ->chunk(500, function ($ace){
                foreach ($ace as $item){
                    $ratio = StockingSkuRatio::getRatio($item->area_psku);
                    if(!$ratio){
                        $flag = false;
                        for($i = 0; $i < AreaSkuEstimate::ESTIMATE_NUM; $i++){
                            if($item->{'estimate_num'.$i} != 0){
                                $flag = true;
                                break;
                            }
                        }
                        if($flag){
                            echo $item->area_psku.PHP_EOL;
                        }
                    }
                }
            });
    }

    private static function _findData(){
            AreaSkuEstimate::query()
                ->orderBy('id')
                ->chunk(500, function ($ace){
                foreach ($ace as $item){
                    $ratio = StockingSkuRatio::getRatio($item->area_psku);
                    if(!$ratio){
                        $flag = false;
                        for($i = 0; $i < AreaSkuEstimate::ESTIMATE_NUM; $i++){
                            if($item->{'estimate_num'.$i} != 0){
                                $flag = true;
                                break;
                            }
                        }
                        if($flag){
                           echo $item->area_psku.PHP_EOL;
                        }
                    }
                }
            });
    }

    private static function _upStockingEsForNAAndUK(){
        AreaClassifyEstimate::query()->whereIn('area_id',[1,2,3])->orderBy('id')
            ->chunk(500, function ($ace){
                foreach ($ace as $_ace){
                    for($i = (AreaSkuEstimate::ESTIMATE_NUM - 1);$i >= 0;$i--){
                        if($i == 0){
                            $_ace->estimate_num0 = 0;
                        }else{
                            $_ace->{'estimate_num'.$i} = $_ace->{'estimate_num'.($i-1)};
                        }
                    }
                    $_ace->save();
                    $classifyId = $_ace->classify_id;
                    //备货sku
                    $ss = StockingSku::query()
                        ->where('area_id',$_ace->area_id)
                        ->whereHas('sku',function ($query) use ($classifyId){
                            $query->where('classify_id',$classifyId);
                        })
                        ->get();
                    foreach ($ss as $_ss){
                        $key = $_ss->area_id.'_'.$_ss->psku_id;
                        $ratio = StockingSkuRatio::getRatio($key);

                        //站点预估
                        $ase = AreaSkuEstimate::query()->firstOrNew(['area_psku' => $key]);
                        for($i = 0;$i < AreaSkuEstimate::ESTIMATE_NUM;$i++){
                            $estimateNum = intval(round($_ace->{'estimate_num'.$i} * $ratio));
                            $_ss->{'estimate_num'.$i} = $estimateNum;
                            $ase->{'estimate_num'.$i} = $estimateNum;
                        }
                        $_ss->save();
                        $ase->save();

                        $_ss->over_stock = StockingSku::getOverStock($_ss);
                        $_ss->over_stock_value = StockingSku::getOverStockValue($_ss->over_stock,$_ss->unit_price);
                        $_ss->save();
                    }
                }
            });
    }

    private static function _upStockingEs(){
        $areaId = 4;
        $res = CountrySkuEstimate::query()->get();
        $sku = [];
        $countrySkuKey = [];
        foreach ($res as $re){
            list($countryId,$pSkuId) = explode('_',$re->area_psku);
            if(!in_array($pSkuId,$sku)){
                $sku[] = $pSkuId;
            }
            if(!array_key_exists($pSkuId,$countrySkuKey)){
                $countrySkuKey[$pSkuId] = [$re->area_psku];
            }else{
                if(!in_array($re->area_psku,$countrySkuKey[$pSkuId])){
                    $countrySkuKey[$pSkuId][] = $re->area_psku;
                }
            }
        }
        $res2 = CountryClassifyEstimate::query()->selectRaw(self::_getSumFields('classify_id'))->groupBy('classify_id')->get()->toArray();
        foreach ($res2 as $re2){
            $classify_id = $re2['classify_id'];
            unset($re2['classify_id']);
            AreaClassifyEstimate::query()->updateOrCreate(
                ['area_id' => $areaId,'classify_id' => $classify_id],$re2
            );
        }
        $classify = [];
        foreach ($sku as $pSkuId){
            $countrySkuEstimateSum = CountrySkuEstimate::query()->selectRaw(self::_getSumFields('1'))
                ->whereIn('area_psku',$countrySkuKey[$pSkuId])->groupBy(1)->first();
            if($countrySkuEstimateSum){
                $data = $countrySkuEstimateSum->toArray();
                unset($data[0]);
                $areaSkuEstimateObj = AreaSkuEstimate::query()->updateOrCreate(
                    ['area_psku' => $areaId.'_'.$pSkuId],$data
                );
                StockingSku::query()->where('area_psku',$areaId.'_'.$pSkuId)->update($data);
                $stockingSkuObj = StockingSku::query()->where('area_psku',$areaId.'_'.$pSkuId)->first();
                if($stockingSkuObj){
                    $stockingSkuObj->over_stock = StockingSku::getOverStock($stockingSkuObj);
                    $stockingSkuObj->over_stock_value = StockingSku::getOverStockValue($stockingSkuObj->over_stock, $stockingSkuObj->unit_price);
                    $stockingSkuObj->save();
                }
                if(!array_key_exists($pSkuId,$classify)){
                    $classify[$pSkuId] = ProductSku::getValueById($pSkuId, 'classify_id');
                }
                $classifyId = $classify[$pSkuId];
                $classifyEstimate = AreaClassifyEstimate::query()->where(['area_id' => $areaId, 'classify_id' => $classifyId])->first();
                $classifyEstimateNum = 0;
                $num = 0;
                for($i = 0; $i < AreaSkuEstimate::ESTIMATE_NUM; $i++){
                    $classifyEstimateNum = $classifyEstimate->{'estimate_num'.$i};
                    if($classifyEstimateNum != 0){
                        $num = $i;
                        break;
                    }
                }
                //EU站点sku销售比例
                $skuRatioObj = StockingSkuRatio::query()->firstOrNew(['area_psku'=> $areaId.'_'.$pSkuId]);
                if($classifyEstimateNum != 0){
                    $stockingSkuRatio = sprintf("%.4f", $areaSkuEstimateObj->{'estimate_num'.$num} / $classifyEstimateNum);
                    $skuRatioObj->ratio = $stockingSkuRatio; //0.3001
                    $skuRatioObj->save();
                }
            }
        }
    }

    private static function _getSumFields($groupBy): string
    {
        //拼接SQL查询字段
        $queryStr = '';
        for($i = 0; $i < AreaSkuEstimate::ESTIMATE_NUM; $i++){
            if ($i < 1) {
                $queryStr .= 'sum(estimate_num'.$i.') as estimate_num'.$i;
            }else{
                $queryStr .= ',sum(estimate_num'.$i.') as estimate_num'.$i;
            }
        }
        return $queryStr.','.$groupBy;
    }
    private static function _upRakutenOrderItem(){
        $n = 0;
        $res = RakutenOrderItem::query()
            ->select('manage_number','variant_id','platform_sku')
            ->distinct()
            ->get();
        $sku = [];
        foreach ($res as $row){
            $count = ProductRakutenSku::query()
                ->where('seller_sku',$row->platform_sku)
                ->where('country_id',12)
                ->count();
            if($count > 0){
                continue;
            }
            $row->variant_id = str_replace(["\r\n","\n","\r"], "", $row->variant_id);
            if(!array_key_exists($row->variant_id, $sku)){
                $sku[$row->variant_id] = ProductSku::getValueByCode($row->variant_id,'id');
            }
            $pSkuId = $sku[$row->variant_id];
            ProductRakutenSku::query()
                ->create(
                    [
                        'manage_number' => $row->manage_number,
                        'variant_id' => $row->variant_id,
                        'seller_sku' => $row->platform_sku,
                        'country_id' => 12,
                        'psku_id' => $pSkuId,
                        'barcode_type' => 3,
                        'barcode' => '无',
                        'account_id' => 200000,
                        'seller_id' => 113,
                        'cuser_id' => 1,
                        'status' => 2,
                        'temp_status' => 2,
                    ]
                );
            $n++;
        }
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForTNSummary(){
        $n = 0;
        TPaymentTNSummary::query()
            ->where('report_date' ,'2024-11-01')
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentDetail::query()
                        ->where('report_date',$_data->report_date)
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForCNSummary(){
        $n = 0;
        TPaymentCNSummary::query()
            ->where('report_date' ,'2024-11-01')
//            ->whereIn('classify_id' ,[63,50])
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentClassify::query()
                        ->where('report_date',$_data->report_date)
                        ->where('classify_id',$_data->classify_id)
                        ->groupBy(['report_date','classify_id'])
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForSeller(){
        $n = 0;
        TPaymentSeller::query()
            ->where('report_date' ,'2024-11-01')
//            ->whereIn('area_id',[3,4,6])
//            ->whereIn('country_id',[4,5,6,7,10,21,12,3])
//            ->whereIn('seller_id',[46,74,85,118,113,120])
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentDetail::query()
                        ->where('report_date',$_data->report_date)
                        ->where('area_id',$_data->area_id)
                        ->where('country_id',$_data->country_id)
                        ->where('seller_id',$_data->seller_id)
                        ->groupBy(['report_date','area_id','country_id','seller_id'])
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForCountry(){
        $n = 0;
        TPaymentCountry::query()
            ->where('report_date' ,'2024-11-01')
//            ->whereIn('area_id',[3,4,6])
//            ->whereIn('country_id',[4,5,6,7,10,21,12,3])
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentDetail::query()
                        ->where('report_date',$_data->report_date)
                        ->where('area_id',$_data->area_id)
                        ->where('country_id',$_data->country_id)
                        ->groupBy(['report_date','area_id','country_id'])
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForArea(){
        $n = 0;
        TPaymentArea::query()
            ->where('report_date' ,'2024-11-01')
//            ->whereIn('area_id',[3,4,6])
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentDetail::query()
                        ->where('report_date',$_data->report_date)
                        ->where('area_id',$_data->area_id)
                        ->groupBy(['report_date','area_id'])
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForClassify(){
        $n = 0;
        TPaymentClassify::query()
            ->where('report_date' ,'2024-11-01')
//            ->whereIn('classify_id' ,[63,50])
//            ->whereIn('area_id',[3,4,6])
//            ->whereIn('country_id',[4,5,6,7,10,21,12,3])
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $re = TPaymentDetail::query()
                        ->where('report_date',$_data->report_date)
                        ->where('classify_id',$_data->classify_id)
                        ->where('area_id',$_data->area_id)
                        ->where('country_id',$_data->country_id)
                        ->where('seller_id',$_data->seller_id)
                        ->groupBy(['report_date','classify_id','area_id','country_id','seller_id'])
//                        ->selectRaw('SUM(t_shipment_price_rmb) AS t_shipment_price_rmb,SUM(profit_rmb) AS profit_rmb')
                        ->selectRaw('SUM(spend_rmb) AS spend_rmb,SUM(profit_rmb) AS profit_rmb,SUM(earning_rmb) AS earning_rmb')
                        ->first();
//                    $_data->t_shipment_price_rmb = $re->t_shipment_price_rmb;
                    $_data->profit_rmb = $re->profit_rmb;
                    $_data->spend_rmb = $re->spend_rmb;
                    $_data->earning_rmb = $re->earning_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailClassifyId(){
        $n = 0;
        $classify = [];
        TPaymentDetail::query()
            ->where('report_date' ,'2024-05-01')
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n,&$classify){
                foreach ($data as $_data){
                    if(!$_data->psku_id){
                        $classifyId = 0;
                    }else{
                        if(!array_key_exists($_data->psku_id,$classify)){
                            $classify[$_data->psku_id] = ProductSku::getValueById($_data->psku_id,'classify_id');
                        }
                        $classifyId = $classify[$_data->psku_id];
                    }
                    $_data->classify_id = $classifyId;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForAll(){
        $n = 0;
        TPaymentDetail::query()
            ->where('report_date' ,'>=','2023-09-01')
            ->orderBy('id')
            ->chunk(1000, function ($data) use(&$n){
                foreach ($data as $_data){
                    $_data->shipment_price_rmb = $_data->shipment_price_v2_rmb;
                    $_data->t_shipment_price_rmb = $_data->t_shipment_price_v2_rmb;
                    $_data->profit_rmb = $_data->profit_v2_rmb;
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForShipmentPriceById(){
        $n = 0;
        TPaymentDetail::query()
            ->whereIn('id' ,[])
            ->orderBy('id')
            ->chunk(800, function ($data) use(&$n){
                foreach ($data as $_data){
                    $_data->t_shipment_price_v2_rmb = round($_data->sales_volume * $_data->shipment_price_v2_rmb,3);
                    if(!$_data->t_shipment_price_v2_rmb){
                        continue;
                    }else{
                        $_data->profit_v2_rmb = round($_data->profit_rmb + $_data->t_shipment_price_rmb - $_data->t_shipment_price_v2_rmb,3);
                    }
                    $_data->save();
                    $n++;
                }
            });
        echo '更新成功：'.$n.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForPurchaseAndShipmentPrice(){
        $n = 0;
        $m = 0;
        $purchasePrice = [];
        $shipmentPrice = [];
        TPaymentDetail::query()
            ->where('report_date' ,'2024-05-01')
            ->orderBy('id')
            ->chunk(800, function ($data) use(&$n,&$m,&$purchasePrice,&$shipmentPrice){
                foreach ($data as $_data){
                    if($_data->is_common == 1 && (!$_data->psku_id)){
                        continue;
                    }else{
                        //采购价格
                        if(!array_key_exists($_data->psku_id,$purchasePrice)){
                            $purchasePrice[$_data->psku_id] = PriceBasics::getPrice($_data->psku_id,$_data->report_date);
                        }
                        $_data->purchase_price_rmb = $purchasePrice[$_data->psku_id];
                        if($_data->area_id){
                            //物流运输价格
                            $lPriceKey = $_data->area_id.'-'.$_data->psku_id;
                            if(!array_key_exists($lPriceKey,$shipmentPrice)){
                                $shipmentPrice[$lPriceKey] = LogisticsBasics::getLogistics($_data->area_id,$_data->psku_id,$_data->report_date);
                            }
                            $_data->shipment_price_rmb = $shipmentPrice[$lPriceKey];
                        }
                        if($_data->sales_volume > 0){
                            $t_purchase_price_rmb_old = $_data->t_purchase_price_rmb;
                            $t_shipment_price_rmb_old = $_data->t_shipment_price_rmb;
                            $profit_rmb_old = $_data->profit_rmb;
                            $_data->t_purchase_price_rmb = round($_data->sales_volume * $_data->purchase_price_rmb,3);
                            $_data->t_shipment_price_rmb = round($_data->sales_volume * $_data->shipment_price_rmb,3);
                            $_data->profit_rmb = round($profit_rmb_old + $t_shipment_price_rmb_old - $_data->t_shipment_price_rmb + $t_purchase_price_rmb_old - $_data->t_purchase_price_rmb,3);
                        }
                        $_data->save();
                        $m++;
                    }
                    $n++;
                }
            });
        echo '运行：'.$n.'条'.'，更新：'.$m.'条'.PHP_EOL;
    }

    private static function _upPaymentDetailForShipmentPrice(){
        $n = 0;
        $m = 0;
        $shipmentPrice = [];
        TPaymentDetail::query()
            ->whereIn('id' ,[1570069,1576696,1572646,1576281,1563126,1572991,1576844,1565548,1570896,1569767,1571034,1569769,1576820,1576828,1575999,1568091,1576156,1576170,1575566,1576021,1576004,1575583,1576424,1575661
            ,1574250,1567435,1575036,1574253,1574793,1558963,1574273,1569415,1574274,1566220,1575798,1573638,1575795,1573641,1573642,1575794,1575793,1573643,1575792,1573644,1575791,1573645,1575790,1573646,1575789,1573647,1575788,1573648,1575787,1573649
            ,1548065,1569791,1539658,1573988,1573987,1573986,1573983,1573981,1573978,1573977])
            ->orderBy('id')
            ->chunk(800, function ($data) use(&$n,&$m,&$shipmentPrice){
                foreach ($data as $_data){
                    if($_data->is_common == 1 && (!$_data->area_id || !$_data->psku_id)){
                        continue;
                    }else{
                        //物流运输价格
                        $lPriceKey = $_data->area_id.'-'.$_data->psku_id.'-'.$_data->report_date;
                        if(!array_key_exists($lPriceKey,$shipmentPrice)){
                            $shipmentPrice[$lPriceKey] = LogisticsBasics::getLogistics($_data->area_id,$_data->psku_id,$_data->report_date);
                        }
                        $_data->shipment_price_rmb = $shipmentPrice[$lPriceKey];
                        if($_data->sales_volume > 0){
                            $t_shipment_price_rmb_old = $_data->t_shipment_price_rmb;
                            $profit_rmb_old = $_data->profit_rmb;
                            $_data->t_shipment_price_rmb = round($_data->sales_volume * $_data->shipment_price_rmb,3);
                            $_data->profit_rmb = round($profit_rmb_old + $t_shipment_price_rmb_old - $_data->t_shipment_price_rmb,3);
                        }
                        $_data->save();
                        $m++;
                    }
                    $n++;
                }
            });
        echo '运行：'.$n.'条'.'，更新：'.$m.'条'.PHP_EOL;
    }

    private static function _checkAmazonSku(){
        $res = DB::connection('mysql_ops')->table('g_product_amazon_sku')
                ->whereIn('country_id',[28,27,29,25,23,22,21,26])
                ->get();
        $count = 0;
        $file = storage_path('app'.DIRECTORY_SEPARATOR.'test').DIRECTORY_SEPARATOR.'亚马逊产品.csv';
            $fp = fopen($file, 'a');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, ['平台SKU','国家','SKU','SKU名称','账号','母ASIN','子ASIN','FNSKU']);
        foreach($res as $re){
            $count++;
            $country = DB::connection('mysql_ops')->table('b_country')->where('id',$re->country_id)->value('country');
            $sku = DB::connection('mysql_ops')->table('g_product_sku')->where('id',$re->psku_id)->first();
            $account = DB::connection('mysql_ops')->table('b_amazon_account')->where('id',$re->account_id)->value('account');
            $temp = [
                    $re->seller_sku,
                    $country,
                    $sku->psku_code ?? '',
                    $sku->psku_cn ?? '',
                    $account,
                    $re->parent_asin,
                    $re->asin,
                    $re->fnsku,
                ];
            fputcsv($fp, $temp);
        }
         fclose($fp);
    }

    private static function _upTransit(){
        $res = PurchaseOrderDetail::query()
            ->whereHas('purchase', function ($query){
                    $query->where('status',2);
                })
            ->where('received_status','<>',3)
            ->selectRaw('sum(order_num-rgoods_num) as transit,psku_id')
            ->groupBy('psku_id')
            ->get();
        $count = 0;
        foreach($res as $re){
            $count++;
            $areaCn = AreaCn::query()->updateOrCreate([
                'area_id' => 9,'psku_id' => $re->psku_id
            ],[
                'transit' => $re->transit,
            ]);
            echo '行'.$count.'已更新'.PHP_EOL;
        }
    }

    private static function _up2Transit(){
        $res = PublicCn::query()->where('area_id',9)->get();
        $count = 0;
        foreach($res as $re){
            $area_transit = AreaCn::query()
                ->where('area_id','<>',9)
                ->where('psku_id',$re->psku_id)
                ->sum('area_transit');
            $total = $re->transit + $re->instant;
            if($total >= $area_transit){
                if($re->transit > $area_transit){
                    $areaCn = AreaCn::query()
                        ->where('area_id',8)
                        ->where('psku_id',$re->psku_id)
                        ->first();
                    if($areaCn){
                        $areaCn->area_transit += $re->transit - $area_transit;
                        $areaCn->save();
                    }else{
                        AreaCn::query()
                            ->create([
                            'area_id' => 8,
                            'psku_id' => $re->psku_id,
                            'transit' => $re->transit - $area_transit,
                        ]);
                    }
                    echo '1.已更新'.$count++.'条'.PHP_EOL;
                }else{
                    echo '1.无需更新'.PHP_EOL;
                }
            }else{
                $temp = $area_transit - $total;
                $areaCn = AreaCn::query()
                    ->where('area_id','<>',9)
                    ->where('psku_id',$re->psku_id)
                    ->where('area_transit','>',0)
                    ->orderBy('area_transit','DESC')
                    ->get();
                foreach($areaCn as $_areaCn){
                    $tempNnm = min($_areaCn->area_transit,$temp);
                    $_areaCn->area_transit -= $tempNnm;
                    $_areaCn->save();
                    echo '2.已更新'.$count++.'条'.PHP_EOL;
                    $temp -= $tempNnm;
                    if($temp == 0)break;
                }
            }
        }
    }

    private static function _checkTransit(){
        $res = PublicCn::query()->where('area_id',9)->get();
        $count = 0;
        foreach($res as $re){
            $count++;
            $area_transit = AreaCn::query()->where('area_id','<>',9)->where('psku_id',$re->psku_id)->sum('area_transit');
            if($area_transit != $re->transit){

            }else{

            }
            $areaCn = AreaCn::query()->updateOrCreate([
                'area_id' => 9,'psku_id' => $re->psku_id
            ],[
                'transit' => $re->transit,
            ]);
            echo '行'.$count.'已更新'.PHP_EOL;
        }
    }

    private static function _upAreaTransit(){
        $file = base_path('在途显示.xlsx');
        $lines = RExcel::importSheet($file);
        unset($lines[0]);
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 3)continue;
            $sku = isset($v[0]) ? trim($v[0]) : '';
            $area = isset($v[1]) ? trim($v[1]) : '';
            $newTransit = isset($v[2]) ? trim($v[2]) : 0;
            if(!$sku || !$area){
                echo '行'.$count.'必填项为空'.PHP_EOL;
                continue;
            }
            $pSkuId = ProductSku::getValueByCode(strtoupper($sku),'id');
            if(!$pSkuId){
                echo '行'.$count.'SKU：'.$sku.'系统不存在'.PHP_EOL;
                continue;
            }
            $areaId = SysArea::getSysAreaIdByArea(strtoupper($area));
            if(!$areaId){
                echo '行'.$count.'站点：'.$area.'系统不存在'.PHP_EOL;
                continue;
            }
            $oldTransit = AreaCn::query()->where('area_id',$areaId)->where('psku_id',$pSkuId)->value('area_transit');
            self::__exeFillData($areaId,$pSkuId,$oldTransit,$newTransit,1);
            echo '行'.$count.'已添加'.PHP_EOL;
        }
    }

    private static function __exeFillData($areaId,$pSkuId,$oldTransit,$newTransit,$userId){
        AreaCn::query()->updateOrCreate(
            ['area_id' => $areaId,'psku_id' => $pSkuId],
            ['area_transit' => $newTransit]
        );
        AreaSkuStock::query()->updateOrCreate(['area_psku' => $areaId.'_'.$pSkuId],['cn_transit_num' => $newTransit]);
        AdjustAreaTransit::query()->create(
            [
                'area_id' => $areaId,
                'psku_id' => $pSkuId,
                'b_num' => $oldTransit ?: 0,
                'a_num' => $newTransit,
                'auser_id' => $userId,
            ]
        );
    }

    private static function _exePaymentDetail(){
        try{
            $date = '2021-10-01';
            $areaId = 4;
            $pSkuId = 1842;
            $start = time();
            $start_memory = memory_get_usage();
            $re = TPaymentDetail::query()
                ->where('report_date',$date)
                ->where('area_id',$areaId)
                ->where('psku_id',$pSkuId)
                ->get();
            $file = storage_path('app'.DIRECTORY_SEPARATOR.'payment').DIRECTORY_SEPARATOR.$date.'-'.$areaId.'-'.$pSkuId.'.csv';
            $fp = fopen($file, 'a');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, ['日期','账号','站点','国家','平台SKU','SKU','运费-前','运费-后','总运费-前','总运费-后','毛利-前','毛利-后']);
            DB::beginTransaction();
            foreach ($re as $_re){
                $shipment_price_rmb_b = $_re->shipment_price_rmb;
                $t_shipment_price_rmb_b = $_re->t_shipment_price_rmb;
                $profit_rmb_b = $_re->profit_rmb;
                $_re->shipment_price_rmb = LogisticsBasics::getLogistics($_re->area_id,$_re->psku_id,$_re->report_date);
                $_re->t_shipment_price_rmb = round($_re->sales_volume * $_re->shipment_price_rmb,3);
                $_re->profit_rmb = round($_re->sales_amount_rmb +
                    $_re->ied_rmb -
                    $_re->t_purchase_price_rmb -
                    $_re->t_shipment_price_rmb +
                    $_re->refund_amount_rmb +
                    $_re->selling_fees_rmb +
                    $_re->fba_fees_rmb +
                    $_re->p_thfba_rmb -
                    $_re->ms_fee_rmb -
                    $_re->lts_fee_rmb +
                    $_re->p_icfee_rmb -
                    $_re->rm_fee_rmb +
                    $_re->rebates_rmb -
                    $_re->spend_rmb -
                    $_re->reimburse_rmb -
                    $_re->reimburse_os_rmb -
                    $_re->reimburse_profit_rmb -
                    $_re->rod_rmb,3);
                $_re->earning_rmb = round($_re->t_purchase_price_rmb +
                    $_re->t_shipment_price_rmb +
                    $_re->profit_rmb,3);
                $_re->save();
                $temp = [
                    $_re->report_date,
                    SysPlatAccount::getAccountById($_re->account_id),
                    SysArea::getSysAreaById($_re->area_id),
                    SysCountry::getCountryById($_re->country_id),
                    $_re->seller_sku,
                    ProductSku::getValueById($_re->psku_id,'psku_code'),
                    $shipment_price_rmb_b,
                    $_re->shipment_price_rmb,
                    $t_shipment_price_rmb_b,
                    $_re->t_shipment_price_rmb,
                    $profit_rmb_b,
                    $_re->profit_rmb,
                ];
                fputcsv($fp, $temp);
            }
            fclose($fp);
            DB::commit();
            $end = time();
            $end_memory = memory_get_usage();
            print_r(($end - $start).'--------'.(($end_memory - $start_memory)/1024/1024)) ;
        }catch (\Exception $e){
            var_dump($e->getMessage());
        }
    }

    private static function _initOsBox(){
        $n = 0;
        InventoryBoxFNSku::query()
            ->orderBy('id')
            ->chunk(100,function ($ds)use (&$n){
                foreach($ds as $d){
                    $n++;
                    //                    echo '---start---：'.$n.'======'.$d->account_id.'_'.$d->warehouse_id.'_'.$d->fnsku.'======='.PHP_EOL;
                    $instant = InventoryBox::query()
                        ->where('account_id',$d->account_id)
                        ->where('warehouse_id',$d->warehouse_id)
                        ->where('fnsku',$d->fnsku)
                        ->sum(DB::raw('(storage_total - out_total) * num_per_box'));
                    if($instant != $d->instant){
                        echo '-----库存不相等------：'.$n.'======'.$d->id.'_'.$d->account_id.'_'.$d->warehouse_id.'_'.$d->fnsku.'======='.PHP_EOL;
                    }else{
                        continue;
                    }
                }
            });
    }

    private static function _initOsData(){
        $n = 0;
        Delivery::query()
            ->whereIn('delivery_no',['OSD22061000002','OSD22061000003'])
            ->orderBy('id')
            ->chunk(100,function ($ds)use (&$n){
                foreach($ds as $d){
                    $n++;
                    echo '---start---：'.$n.'======'.$d->delivery_no.'======='.PHP_EOL;
                    $DeliveryBoxes = DeliveryBox::query()
                        ->where('delivery_no',$d->delivery_no)
                        ->get();
                    $total = 0;
                    foreach($DeliveryBoxes as $DeliveryBox){
                        $ship_num = DeliveryDetail::query()
                            ->where('delivery_no',$DeliveryBox->delivery_no)
                            ->where('fnsku',$DeliveryBox->fnsku)
                            ->value('ship_num');
                        $DeliveryBoxTotal = intdiv($ship_num,$DeliveryBox->num_per_box);
                        $total += $DeliveryBoxTotal;
                        $DeliveryBox->total = $DeliveryBoxTotal;
                        $DeliveryBox->save();
                        for($index = 1; $index <= $DeliveryBoxTotal;$index++){
                            $DeliveryBoxDetailModel = DeliveryBoxDetail::query()->create([
                                'delivery_box_id' => $DeliveryBox->id,
                                'p_index' => $index,
                                'status' => 1,
                                'created_at' => $d->created_at,
                                'updated_at' => $d->updated_at,
                            ]);
                            if(!$DeliveryBoxDetailModel){
                                echo '---发货单明细装箱明细信息创建失败---：============='.PHP_EOL;
                                break;
                            }
                        }
                    }
                    $d->total_box = $total;
                    $d->save();
                }
            });
    }

    private static function _upLeTian(){
        $file = base_path('pp.xlsx');
        $lines = RExcel::importSheet($file);
        unset($lines[0]);
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 2)continue;
            $sku = isset($v[0]) ? trim($v[0]) : '';
            $quantity = isset($v[1]) ? trim($v[1]) : '';
            $pSkuId = ProductSku::getValueByCode($sku,'id');
            if(!$pSkuId){
                echo '行'.$count.'SKU'.$sku.'系统不存在'.PHP_EOL;
                continue;
            }
            $flag = OrderBasics::query()->create(['b_date' => '2022-05-01',
                'country_id' => 12,
                'seller_sku' => 'lt-'.$sku,
                'quantity' => $quantity,
                'area_id' => 6,
                'psku_id' => $pSkuId]);
            if($flag){
                echo '行'.$count.'已更新'.PHP_EOL;
            }else{
                echo '行'.$count.'更新失败'.PHP_EOL;
            }
        }
    }

    private static function _upOrderFee(){
        //        $datetime = '4 de fev. de 2022 19:10:13 GMT-8';
        //        dd(str_ends_with($datetime,".00"));
        //        dd(stripos($datetime,"/") == 2);
        //        $datetime = self::_dateFormat($datetime);
        //        dd($datetime);
        //        dd(strtotime($datetime));
        //        dd(gmdate('Y-m-d H:i:s',strtotime($datetime)));
        $file = base_path('data.xlsx');
        $lines = RExcel::importSheet($file);
        unset($lines[0]);
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 2)continue;
            $datetime = isset($v[0]) ? trim($v[0]) : '';
            $orderId = isset($v[1])&&$v[1] ? trim($v[1]) : '';
            if(!$datetime || !$orderId){
                echo '行'.$count.'必填项为空'.PHP_EOL;
                continue;
            }
            $countOrder = TmpOrder::query()->where('order_id',$orderId)->count();
            if($countOrder > 0){
                echo '行'.$count.'已存在'.PHP_EOL;
                continue;
            }
            $datetime = self::__dateFormat($datetime);
            $order = TmpOrder::query()->create([
                'order_id' => $orderId,
                'purchase_date' => gmdate('Y-m-d H:i:s',strtotime($datetime)),
            ]);
            if($order){
                echo '行'.$count.'已添加'.PHP_EOL;
            }else{
                echo '行'.$count.'添加失败'.PHP_EOL;
            }
        }
    }

    private static function __dateFormat($datetime){
        $datetime = str_replace(['a.m.','p.m.'], ['am','pm'],$datetime);
        if(str_ends_with($datetime,".00") && (stripos($datetime,"/") == 2)){
            $datetime = str_replace(".", ':',$datetime);
        }
        $datetime = str_replace(['Jan.','de jan. de','sty','ene.','ene','janv.','gen','Oca'], 'Jan',$datetime);
        $datetime = str_replace(['Feb.','de fev. de','feb.','févr.','feb','Şub','lut'], 'Feb',$datetime);
        $datetime = str_replace(['Mar.','de mar. de','mar.','mars','mar','mrt.'], 'Mar',$datetime);
        $datetime = str_replace(['Apr.','de abr. de','abr','abr.','avr.','apr.','apr','kwi','Nis'], 'Apr',$datetime);
        $datetime = str_replace(['may.','de mai. de','mai','mag','mei','maj'], 'May',$datetime);
        $datetime = str_replace(['Jun.','de jun. de','jun.','juin','giu','cze','Haz','juni'], 'Jun',$datetime);
        $datetime = str_replace(['Jul.','de jul. de','jul.','juil.','lug','lip','juli','Tem'], 'Jul',$datetime);
        $datetime = str_replace(['Aug.','de ago. de','Ağu','ago.','ago','août','sie'], 'Aug',$datetime);
        $datetime = str_replace(['Sep.','sept.','de set. de','set','sep.','Eyl','wrz'], 'Sep',$datetime);
        $datetime = str_replace(['Oct.','oct.','ott','okt.','paź','Eki'], 'Oct',$datetime);
        $datetime = str_replace(['Nov.','de nov. de','nov.','nov','lis','Kas'], 'Nov',$datetime);
        $datetime = str_replace(['Dec.','de dez. de','dic.','déc.','dic','gru','dec.','Ara'], 'Dec',$datetime);
        $datetime = str_replace([".","/"], '-',$datetime);
        return $datetime;
    }

    public static function work($func,$param)
    {
        ini_set('memory_limit', '1024M');
        set_time_limit(0);
        if($param){
            self::{'_'.$func}($param);
        }else{
            self::{'_'.$func}();
        }
    }
}
